/**
 * 项目名：phms3
 * 日期：    2019-11-22 17:31:06
 * Copyright (c) 2015- HTSC-版权所有
 */

package com.joysuch.wwyt.mh.repository;

import com.joysuch.wwyt.mh.entity.MhMajorHazard;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

import javax.persistence.Tuple;
import java.util.List;
import java.util.Optional;

/**
 * MhMajorHazardDao
 */
public interface MhMajorHazardDao extends JpaRepository<MhMajorHazard, Long>, QuerydslPredicateExecutor<MhMajorHazard> {

    public Page<MhMajorHazard> findAll(Specification<MhMajorHazard> spec, Pageable pageable);

    public MhMajorHazard save(MhMajorHazard bean);

    public void delete(MhMajorHazard bean);

    //    @Query("select d.name as name,count(*) as value from MhMajorHazard m,RiskDistrict d where m.riskDistrictId=d.id group by m.riskDistrictId")
    @Query(value = " SELECT d.NAME AS name ,count(*) AS value FROM wwyt_mh_major_hazard AS m,wwyt_risk_district AS d WHERE m.RISK_DISTRICT_ID = d.ID " +
            "AND m. DELETE_FLAG ='0' AND (m.state IS NULL OR m.state !=44) AND d. DELETE_FLAG ='0' AND (d.state IS NULL OR d.state !=44) GROUP BY m.RISK_DISTRICT_ID", nativeQuery = true)
    public List<Tuple> statDangerCount();


    @Query(value = "select h.* from wwyt_mh_major_hazard h where  h.DELETE_FLAG ='0' and h.code =?1 AND (h.state IS NULL OR h.state !=44)", nativeQuery = true)
    MhMajorHazard findByCode(String code);

    @Query("select count(m) from MhMajorHazard m where name = ?1")
    long countOfName(String name);

    //@Query("select count(m) from MhMajorHazard m where name = ?1 and id != ?2")
    @Query(value = " SELECT count(1) FROM wwyt_mh_major_hazard WHERE NAME =?1 AND ID !=?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    long countOfNameExcludeId(String name, Long id);

    @Query(value = "select id from wwyt_mh_major_hazard where  DELETE_FLAG ='0' and name =?1 AND (state IS NULL OR state !=44)", nativeQuery = true)
    public Long findIdByName(String name);

    // String findNameById(Long chemProductId);

    @Query(value = "SELECT * FROM wwyt_mh_major_hazard WHERE DELETE_FLAG = '0' and NAME =?1 and (state IS NULL OR state !=44)", nativeQuery = true)
    List<MhMajorHazard> findByNameAndState(String name);

    @Query(value = "SELECT h.* FROM wwyt_mh_major_hazard h WHERE h.DELETE_FLAG = '0' and (h.state IS NULL OR h.state !=44)", nativeQuery = true)
    List<MhMajorHazard> findAll();

    @Query(value = "select * from wwyt_mh_major_hazard where name =?1 and id!=?2 and DELETE_FLAG ='0' and (state is null or state !=44)", nativeQuery = true)
    List<MhMajorHazard> findByNameAndStateAndIdNotEq(String name, Long id);

    @Query(value = "select * from wwyt_mh_major_hazard where id =?1 and DELETE_FLAG ='0' and (state is null or state !=44)",nativeQuery = true)
    Optional<MhMajorHazard> findByIdAndEfficient(Long majorHazardId);

    @Modifying
    @Query(value = "UPDATE wwyt_bp_production_facility SET MAJOR_HAZARD_ID = NULL , MAJOR_HAZARD_UNIT = NULL , MAJOR_HAZARD_UNIT_CODE = NULL WHERE MAJOR_HAZARD_ID =?1 and DELETE_FLAG ='0' and (state is null or state !=44)", nativeQuery = true)
    void deleteProductionFacility(Long majorHazardId);

    @Modifying
    @Query(value = "UPDATE wwyt_bp_storage_tank SET MAJOR_HAZARD_ID = NULL WHERE MAJOR_HAZARD_ID = ?1 and DELETE_FLAG ='0'", nativeQuery = true)
    void deleteStorageTank(Long majorHazardId);

    @Modifying
    @Query(value = "UPDATE wwyt_bp_storage_tank_area SET MAJOR_HAZARD_ID = NULL WHERE MAJOR_HAZARD_ID = ?1 and DELETE_FLAG ='0'", nativeQuery = true)
    void deleteStorageTankArea(Long majorHazardId);

    @Modifying
    @Query(value = "UPDATE wwyt_bp_store_house SET MAJOR_HAZARD_ID = NULL , MAJOR_HAZARD_UNIT = NULL , MAJOR_HAZARD_UNIT_CODE = NULL WHERE MAJOR_HAZARD_ID = ?1 and DELETE_FLAG ='0'", nativeQuery = true)
    void deleteWarehouse(Long majorHazardId);

    @Modifying
    @Query(value = "UPDATE wwyt_mh_major_hazard_chem_product_rel SET DELETE_FLAG = '1' WHERE MAJOR_HAZARD_ID = ?1", nativeQuery = true)
    void deleteHazardousChemicals(Long majorHazardId);

    @Query(value = "select f.* from wwyt_mh_major_hazard f where (f.state is null or f.state !=44) and f.location is not null AND f.DELETE_FLAG='0'", nativeQuery = true)
    List<MhMajorHazard> findAllHaveLocationForTank();


    @Query(value = "select * from wwyt_mh_major_hazard where id in (?1) and DELETE_FLAG ='0' and (state is null or state !=44)", nativeQuery = true)
    List<MhMajorHazard> findByIds(List<Long> majorHazardIds);

    @Query(value = "select count(*) from wwyt_mh_major_hazard_task ht LEFT JOIN wwyt_mh_major_hazard_plan hp on ht.plan_id = hp.id left join wwyt_mh_major_hazard mh on hp.major_hazard_id = mh.id\n" +
            "where ht.start_time > now() and mh.id = ?1\n" +
            "and ht.delete_flag = '0' and hp.delete_flag = '0'", nativeQuery = true)
    Integer getTaskNum(Long hazardId);

}
